﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GIMS.MODEL;

namespace KPIS.GERP.GIMS.DAL
{
    public sealed class DeliveryNoteRouteDAL
    {
        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public void Insert(IList<DeliveryNoteRouteInfo> infoList, int dn_seq, int user_id, IDBManager dbManagerTrans)
        {
            string strSQL;
            DataSet ds;
            Nullable<int> lastSeq = null;
            try
            {
                IList<DeliveryNoteRouteInfo> infoListOld = new List<DeliveryNoteRouteInfo>();
                infoListOld = GetDeliveryNoteRoute(dn_seq, dbManagerTrans);
                CopyToHistory(infoListOld, user_id, dbManagerTrans);

                for (int i = 0; i < infoList.Count; ++i)
                {
                    dbManagerTrans.CreateParameters(11);
                    dbManagerTrans.AddParameters(0, "@icm_dn_seq", infoList[i].InfoDn.Seq);
                    dbManagerTrans.AddParameters(1, "@from_emp_seq", infoList[i].InfoFromEmp.Seq);
                    dbManagerTrans.AddParameters(2, "@to_emp_seq", infoList[i].InfoToEmp.Seq);
                    dbManagerTrans.AddParameters(3, "@remark", infoList[i].Remark);
                    dbManagerTrans.AddParameters(4, "@order_seq", infoList[i].Order);
                    dbManagerTrans.AddParameters(5, "@upper_seq", (infoList[i].Type == "APPROVE") ? lastSeq : null);
                    dbManagerTrans.AddParameters(6, "@route_type", infoList[i].Type);
                    dbManagerTrans.AddParameters(7, "@active", infoList[i].Active);
                    dbManagerTrans.AddParameters(8, "@route_status", infoList[i].InfoRouteStatus.Status);
                    dbManagerTrans.AddParameters(9, "@created_by", infoList[i].InfoRecord.Created_by);
                    dbManagerTrans.AddParameters(10, "@created_when", infoList[i].InfoRecord.Created_when);

                    strSQL = "INSERT INTO ICM_DELIVERY_NOTE_ROUTE"
                            + " (ICM_DN_SEQ"
                                + ", FROM_EMP_SEQ"
                                + ", TO_EMP_SEQ"
                                + ", REMARK"
                                + ", ORDER_SEQ"
                                + ", UPPER_ICM_DN_ROUTE_SEQ"
                                + ", ROUTE_TYPE"
                                + ", ROUTE_STATUS"
                                + ", CREATED_BY"
                                + ", CREATED_WHEN)"
                            + " VALUES(@icm_dn_seq"
                                + ", @from_emp_seq"
                                + ", @to_emp_seq"
                                + ", @remark"
                                + ", @order_seq"
                                + ", @upper_seq"
                                + ", @route_type"
                                + ", @route_status"
                                + ", @created_by"
                                + ", @created_when)";

                    dbManagerTrans.ExecuteNonQuery(CommandType.Text, strSQL);
                    strSQL = "SELECT LAST_INSERT_ID()"
                        + " FROM ICM_DELIVERY_NOTE_ROUTE";
                    ds = dbManagerTrans.ExecuteDataSet(CommandType.Text, strSQL);
                    if (ds.Tables[0].Rows.Count > 0)
                    {
                        lastSeq = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public IList<DeliveryNoteRouteInfo> GetDeliveryNoteRoute(int dn_seq, IDBManager dbManagerTrans)
        {
            string strSQL;
            DataSet ds;
            DataRowCollection dr;
            IList<DeliveryNoteRouteInfo> infoList = new List<DeliveryNoteRouteInfo>();
            try
            {
                dbManagerTrans.CreateParameters(1);

                dbManagerTrans.AddParameters(0, "@icm_dn_seq", dn_seq);

                strSQL = "SELECT r.*"
                            + ", rs.ROUTE_STATUS_NAME"
                        + " FROM ICM_DELIVERY_NOTE_ROUTE r"
                            + " INNER JOIN SYS_ROUTE_STATUS rs ON r.ROUTE_STATUS = rs.ROUTE_STATUS"
                        + " WHERE r.ICM_DN_SEQ = @icm_dn_seq";

                ds = dbManagerTrans.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        DeliveryNoteRouteInfo info = new DeliveryNoteRouteInfo();
                        DeliveryNoteInfo infoDn = new DeliveryNoteInfo();
                        HrmEmployeeInfo infoFromEmp = new HrmEmployeeInfo();
                        HrmEmployeeInfo infoToEmp = new HrmEmployeeInfo();
                        SysRouteStatusInfo infoStatusRoute = new SysRouteStatusInfo();

                        info.Seq = Convert.ToInt32(dr[i]["ICM_DN_ROUTE_SEQ"].ToString());

                        infoDn.Seq = Convert.ToInt32(dr[i]["ICM_DN_SEQ"].ToString());
                        info.InfoDn = infoDn;

                        infoFromEmp.Seq = Convert.ToInt32(dr[i]["FROM_EMP_SEQ"].ToString());
                        info.InfoFromEmp = infoFromEmp;

                        infoToEmp.Seq = Convert.ToInt32(dr[i]["TO_EMP_SEQ"].ToString());
                        info.InfoToEmp = infoToEmp;

                        info.Type = (dr[i]["ROUTE_TYPE"]).ToString();

                        info.Remark = (dr[i]["REMARK"]).ToString();

                        info.Order = (Convert.IsDBNull(dr[i]["ORDER_SEQ"]) ? null : (Nullable<int>)(dr[i]["ORDER_SEQ"]));

                        info.UpperSeq = (Convert.IsDBNull(dr[i]["UPPER_ICM_DN_ROUTE_SEQ"]) ? null : (Nullable<int>)(dr[i]["UPPER_ICM_DN_ROUTE_SEQ"]));

                        infoStatusRoute.Name = (dr[i]["ROUTE_STATUS_NAME"]).ToString();
                        info.InfoRouteStatus = infoStatusRoute;

                        info.Status = (dr[i]["ROUTE_STATUS"]).ToString();

                        infoList.Add(info);
                    }
                }
                return infoList;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        private void CopyToHistory(IList<DeliveryNoteRouteInfo> infoList, int user_id, IDBManager dbManagerTrans)
        {
            try
            {
                DeliveryNoteRouteHistoryDAL dalRouteHis = new DeliveryNoteRouteHistoryDAL();
                if (infoList.Count == 0)
                    return;
                int TmpSeq = (infoList[0].InfoDn.Seq == null) ? 0 : (int)infoList[0].InfoDn.Seq;
                int TmpRevisionNo = dalRouteHis.GetNextRevisionNo(dbManagerTrans, TmpSeq);
                IList<DeliveryNoteRouteHistoryInfo> infoListHistory = new List<DeliveryNoteRouteHistoryInfo>();
                foreach (DeliveryNoteRouteInfo current in infoList)
                {
                    DeliveryNoteRouteHistoryInfo infoHistory = new DeliveryNoteRouteHistoryInfo();
                    infoHistory.RevisionNo = TmpRevisionNo;
                    infoHistory.InfoFromEmp = current.InfoFromEmp;
                    infoHistory.InfoToEmp = current.InfoToEmp;
                    infoHistory.Remark = current.Remark;
                    infoHistory.Order = current.Order;
                    infoHistory.UpperSeq = current.UpperSeq;
                    infoHistory.Status = current.Status;
                    infoHistory.Type = current.Type;
                    RecordInfo InfoRecord = new RecordInfo();
                    InfoRecord.Created_by = user_id;
                    InfoRecord.Created_when = DateTime.Now;
                    infoHistory.InfoRecord = InfoRecord;
                    infoHistory.InfoDn = current.InfoDn;
                    infoListHistory.Add(infoHistory);
                }
                dalRouteHis.Insert(infoListHistory, dbManagerTrans);
                DeleteAll(infoList, dbManagerTrans);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void DeleteAll(IList<DeliveryNoteRouteInfo> infoList, IDBManager dbManagerTrans)
        {
            try
            {
                string strSQL;
                string seq = "";
                for (int i = 0; i < infoList.Count; ++i)
                {
                    seq += infoList[i].Seq + ",";
                }
                seq = seq.Substring(0, seq.Length - 1);

                strSQL = "DELETE FROM ICM_DELIVERY_NOTE_ROUTE WHERE ICM_DN_ROUTE_SEQ IN (" + seq + ")";

                dbManagerTrans.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }
}
